USE HierarchyIdDemo
GO

-- add PROCEDURE to chart hierarchy beneath
-- any department
CREATE PROCEDURE SelfJoin.ShowDepartmentChart
	@DepartmentID INT
AS
BEGIN

	DECLARE @OrgChart TABLE (
		DepartmentID INT, 
		ParentID INT, 
		Level INT,
		Path VARCHAR(255) )

	DECLARE @Level AS INT
	SET @Level = 0

	-- add the top level
	INSERT into @OrgChart
		SELECT DepartmentID, 
				ParentID, 
				@Level, 
				REPLACE(STR(DepartmentID, 5), ' ', '0') AS Path
			FROM SelfJoin.Org
			WHERE DepartmentID = @DepartmentID

	-- explode each child level
	WHILE (1=1)
	BEGIN

		INSERT into @OrgChart
			SELECT SelfJoin.Org.DepartmentID, 
					SelfJoin.Org.ParentID, 
					Level + 1, 
					Path + '.' + REPLACE(STR(SelfJoin.Org.DepartmentID, 5), ' ', '0') AS Path
				FROM SelfJoin.Org
					JOIN @OrgChart OrgChart 
						ON SelfJoin.Org.ParentID = OrgChart.DepartmentID
				WHERE EXISTS (
					SELECT *
						FROM @OrgChart TestOrgChart
						WHERE Level = @Level
							AND SelfJoin.Org.ParentID = TestOrgChart.DepartmentID)

		IF @@rowcount = 0
			BREAK

		SET @Level = @Level + 1

	END

	SELECT Path,
			space(Level * 5) + DepartmentName Department
		FROM @OrgChart OrgChart
			JOIN SelfJoin.Org
				ON OrgChart.DepartmentID = SelfJoin.Org.DepartmentID
		ORDER BY Path

END
GO

EXEC SelfJoin.ShowDepartmentChart 2
EXEC SelfJoin.ShowDepartmentChart 1
GO
